package com.rybbaby.tss.front.tssStatistics.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.BeanUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssStatistics.dao.IViewOtherStatisticsDao;
import com.rybbaby.tss.front.tssStatistics.entity.ViewOtherStatistics;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description 员工表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:25:24
 */
@Repository
public class ViewOtherStatisticsDaoImp extends GenericDao<ViewOtherStatistics> implements IViewOtherStatisticsDao {


    @Override
    public PageBean pageList(HashMap<String, Object> paramHashMap, int pageNo, int pageSize) throws InstantiationException, IllegalAccessException {
        String id = (String)paramHashMap.get("id");
        String adminUserId = (String)paramHashMap.get("adminUserId");
        String paymentCreateTime_begin = (String)paramHashMap.get("paymentCreateTime_begin");
        String paymentCreateTime_end = (String)paramHashMap.get("paymentCreateTime_end");

        String idSql = "";
        String adminUserIdSql = "";
        String paymentCreateTime_beginSql = "";
        String paymentCreateTime_endSql = "";
        String refundCreateTime_begin = "";
        String refundCreateTime_end = "";
        if(id!=null && !id.equals("")){
            idSql = "\tAND sys_admin_user.id = '"+id+"'\n";
        }
        if(adminUserId!=null && !adminUserId.equals("")){
            adminUserIdSql = "\tAND sys_admin_user.id in ('"+adminUserId+"')\n";
        }
        if(paymentCreateTime_begin!=null && !paymentCreateTime_begin.equals("")){
            paymentCreateTime_beginSql = "\tAND unix_timestamp(tss_payment.createTime) >= unix_timestamp('"+paymentCreateTime_begin+"')\n";
            refundCreateTime_begin = "\tAND unix_timestamp(tss_member_return_premium.updateTime) >= unix_timestamp('"+paymentCreateTime_begin+"')\n";
        }
        if(paymentCreateTime_end!=null && !paymentCreateTime_end.equals("")){
            paymentCreateTime_endSql = "\tAND unix_timestamp(tss_payment.createTime) <= unix_timestamp('"+paymentCreateTime_end+"')\n";
            refundCreateTime_end = "\tAND unix_timestamp(tss_member_return_premium.updateTime) <= unix_timestamp('"+paymentCreateTime_end+"')\n";
        }

        String sql = "SELECT\n" +
                "\tC.id,\n" +
                "\tC.realName,\n" +
                "\tCOUNT(A.id) AS numPayments,\n" +
                "\tIFNULL(SUM(A.amount),0)AS sumAmount,\n" +
                "\tCOUNT(B.id) AS numRefunds,\n" +
                "\tIFNULL(SUM(B.amout),0)AS sumAmout\n" +
                "FROM(\n" +
                "\tSELECT\n" +
                "\t\t\tsys_admin_user.id AS id,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName\n" +
                "\t\tFROM\n" +
                "\t\t\tsys_admin_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tsys_admin_user.post = '4'\n" +
                "\t\t\tFIND_IN_SET( 4, sys_admin_user.partTime) \n" +
                idSql +
                adminUserIdSql +
                ")C\n" +
                "LEFT JOIN\n" +
                "(\n" +
                "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\ttss_payment\n" +
                "WHERE tss_payment.paymentType = 6\n" +
                paymentCreateTime_beginSql +
                paymentCreateTime_endSql +
                ")A\n" +
                "ON C.id = A.adminUserId\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "SELECT \n" +
                "\t* \n" +
                "FROM\n" +
                "tss_member_return_premium \n" +
                "WHERE tss_member_return_premium.refundStatus = 5\n" +
                refundCreateTime_begin +
                refundCreateTime_end +
                ")B\n" +
                "ON A.id = B.paymentId\n" +
                "GROUP BY C.id\n";
        System.out.println(sql);
        PageBean pageBean = this.pageList(sql, pageNo, pageSize);
        List<ViewOtherStatistics> populate = BeanUtils.populate(ViewOtherStatistics.class, (List<Map<String, Object>>) pageBean.getList(), "sumPerformance", "attendanceRate");
        pageBean.setList(populate);
        return pageBean;
    }
}
